--POUR CONTROLE

define erp='erp_mli2.';
define outils='outils_mli2.';

 --CALL uvse_ExistsOrCreate('VRT_CHAPVRT');

 DELETE FROM se_VRT_CHAPVRT;

 --SELECT * FROM &erp.VRT_CHAPVRT
 --SELECT * FROM uv_pparam_masques
 /*
select * from (
SELECT &outils.stragg(source) source,chap_cod, vrt_cod, enreg_id, chapvrt_rang, enreg_s, chapvrt_lib, obligvrt_ok
FROM (
  SELECT 'uv' source ,g.* FROM se_VRT_CHAPVRT g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.VRT_CHAPVRT g
  )

GROUP BY chap_cod, vrt_cod, enreg_id, chapvrt_rang, enreg_s, chapvrt_lib, obligvrt_ok
HAVING Count(1)!=2 ) r
ORDER BY chap_cod,vrt_cod  */


 INSERT INTO se_VRT_CHAPVRT
 (SELECT 'MLI' chap_cod,
         v.vrt_cod,
         0 enreg_id,
         9999 chapvrt_rang,
         'N' enreg_s,
         NULL chapvrt_lib,
         v.obligvrt_ok
     FROM  se_VRT_VRT v

     );



 UPDATE se_VRT_CHAPVRT tf SET enreg_id=Nvl((select enreg_id FROM &erp.VRT_CHAPVRT f WHERE f.chap_cod=tf.chap_cod AND f.vrt_cod=tf.vrt_cod),0) ;
 UPDATE se_VRT_CHAPVRT tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;

/*reprise des rangs des vrt dans le chapitre et attribution des nouveau rang des nouvelles vrt*/
 UPDATE se_VRT_CHAPVRT tf SET CHAPVRT_RANG=Nvl((select CHAPVRT_RANG FROM &erp.VRT_CHAPVRT f WHERE f.chap_cod=tf.chap_cod AND f.vrt_cod=tf.vrt_cod),9999) ;
 UPDATE se_VRT_CHAPVRT tf SET CHAPVRT_RANG =(
    SELECT rang FROM (
        SELECT chap_cod,vrt_cod,Rank() OVER (PARTITION BY 1 ORDER BY CHAPVRT_RANG,chap_cod,vrt_cod nulls last) rang
        FROM se_VRT_CHAPVRT ) f
    WHERE f.chap_cod=tf.chap_cod AND f.vrt_cod=tf.vrt_cod) ;



 commit;





